import pymysql
from pymysql.cursors import DictCursor
import os
from backend.config.db_config import DB_CONFIG

# 读取SQL文件
def read_sql_file(file_path):
    with open(file_path, 'r', encoding='utf-8') as file:
        return file.read()

# 初始化MySQL数据库
def initialize_database():
    try:
        print("尝试连接到MySQL服务器...")
        
        # 首先连接到MySQL服务器（不指定数据库）
        connection = pymysql.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            port=DB_CONFIG['port'],
            cursorclass=DictCursor
        )
        
        print("成功连接到MySQL服务器！")
        cursor = connection.cursor()
        
        # 创建数据库
        db_name = DB_CONFIG['database']
        print(f"正在创建数据库: {db_name}...")
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
        print(f"数据库 {db_name} 创建成功！")
        
        # 选择数据库
        cursor.execute(f"USE {db_name};")
        print(f"已选择数据库: {db_name}")
        
        # 读取并执行SQL文件
        sql_file_path = os.path.join('backend', 'database_setup.sql')
        print(f"正在读取SQL文件: {sql_file_path}...")
        sql_content = read_sql_file(sql_file_path)
        
        # 分割SQL语句（注意处理分号后面的换行）
        sql_statements = []
        current_statement = ''
        
        for line in sql_content.split('\n'):
            line = line.strip()
            # 忽略注释和空行
            if line.startswith('--') or not line:
                continue
            
            current_statement += line + ' '
            # 当遇到分号时，执行当前语句
            if line.endswith(';'):
                # 移除最后的分号
                current_statement = current_statement.rstrip(';')
                # 跳过USE语句，因为我们已经选择了数据库
                if not current_statement.strip().upper().startswith('USE'):
                    # 跳过CREATE DATABASE语句，因为我们已经创建了数据库
                    if not current_statement.strip().upper().startswith('CREATE DATABASE'):
                        sql_statements.append(current_statement)
                current_statement = ''
        
        # 执行所有SQL语句
        for i, sql in enumerate(sql_statements, 1):
            print(f"执行SQL语句 {i}/{len(sql_statements)}...")
            try:
                cursor.execute(sql)
                connection.commit()
            except Exception as e:
                print(f"执行SQL语句失败: {sql}\n错误: {e}")
                connection.rollback()
        
        # 添加用户ID为4的测试用户（与前端测试中使用的用户ID一致）
        print("\n添加测试用户（用户ID: 4）...")
        try:
            # 先检查用户是否已存在
            cursor.execute("SELECT user_id FROM user WHERE user_id = %s", (4,))
            user_exists = cursor.fetchone()
            
            if not user_exists:
                cursor.execute(
                    "INSERT INTO user (user_id, username, password, role, name, phone, address) VALUES (%s, %s, %s, %s, %s, %s, %s)",
                    (4, 'testuser', 'password123', 'student', '测试用户', '13800138004', '学生公寓2号楼402室')
                )
                connection.commit()
                print("测试用户添加成功！")
            else:
                print("测试用户已存在！")
        except Exception as e:
            print(f"添加测试用户失败: {e}")
            connection.rollback()
        
        # 为用户ID 4添加测试订单
        print("\n为用户ID 4添加测试订单...")
        try:
            # 先检查是否已有订单
            cursor.execute("SELECT COUNT(*) as count FROM `order` WHERE user_id = %s", (4,))
            order_count = cursor.fetchone()['count']
            
            if order_count == 0:
                # 插入测试订单
                # 订单1（待支付）
                cursor.execute(
                    "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (%s, %s, %s)",
                    (4, '待支付', 113.00)
                )
                order_id1 = cursor.lastrowid
                
                # 订单2（已支付）
                cursor.execute(
                    "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (%s, %s, %s)",
                    (4, '已支付', 75.00)
                )
                order_id2 = cursor.lastrowid
                
                # 订单3（已完成）
                cursor.execute(
                    "INSERT INTO `order` (user_id, order_status, total_amount) VALUES (%s, %s, %s)",
                    (4, '已完成', 105.00)
                )
                order_id3 = cursor.lastrowid
                
                print(f"已添加3个测试订单，订单ID: {order_id1}, {order_id2}, {order_id3}")
                
                # 添加订单详情
                # 订单1详情
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id1, 1, 1, 88.00, 88.00)
                )
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id1, 4, 2, 10.00, 20.00)
                )
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id1, 5, 1, 20.00, 20.00)
                )
                
                # 订单2详情
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id2, 2, 10, 5.00, 50.00)
                )
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id2, 3, 1, 15.00, 15.00)
                )
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id2, 4, 1, 10.00, 10.00)
                )
                
                # 订单3详情
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id3, 1, 1, 88.00, 88.00)
                )
                cursor.execute(
                    "INSERT INTO order_detail (order_id, food_id, quantity, unit_price, subtotal) VALUES (%s, %s, %s, %s, %s)",
                    (order_id3, 3, 1, 15.00, 15.00)
                )
                
                connection.commit()
                print("订单详情添加成功！")
            else:
                print(f"用户ID 4已有{order_count}个订单，跳过添加")
        except Exception as e:
            print(f"添加测试订单失败: {e}")
            connection.rollback()
        
        # 验证数据库内容
        print("\n验证数据库内容:")
        
        # 查询用户表
        cursor.execute("SELECT user_id, username, role FROM user")
        users = cursor.fetchall()
        print("\n用户列表:")
        for user in users:
            print(f"用户ID: {user['user_id']}, 用户名: {user['username']}, 角色: {user['role']}")
        
        # 查询用户ID 4的订单
        cursor.execute("SELECT order_id, order_status, total_amount FROM `order` WHERE user_id = %s", (4,))
        orders = cursor.fetchall()
        print("\n用户ID 4的订单:")
        for order in orders:
            print(f"订单ID: {order['order_id']}, 状态: {order['order_status']}, 总金额: {order['total_amount']}")
        
        # 查询订单详情
        cursor.execute("SELECT order_id, food_id, quantity FROM order_detail LIMIT 5")
        details = cursor.fetchall()
        print("\n订单详情示例:")
        for detail in details:
            print(f"订单ID: {detail['order_id']}, 菜品ID: {detail['food_id']}, 数量: {detail['quantity']}")
        
        print("\nMySQL数据库初始化完成！")
        
    except pymysql.MySQLError as e:
        print(f"MySQL错误: {e}")
    except Exception as e:
        print(f"发生错误: {e}")
    finally:
        if 'connection' in locals() and connection.open:
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    initialize_database()